--1
select team,count(*) as wincount 
from (select team,(year-row_number() over (partition by team order by year asc)) as gid from t1) t2
group by team,gid
having wincount>=3;
--2
select * 
from (select id,time,price,case when price<lagprice and price<leadprice then '波谷'
                          when price>lagprice and price>leadprice then '波峰'
                     else null end as feature
      from (select id, time, price,
                   nvl(lag(price) over(partition by id order by time),0) lagprice,
                   nvl(lead(price) over(partition by id order by time),0) leadprice
            from t2
      )t3
)t4
where feature is not null;

--3.1
select id,(unix_timestamp(max(dt),'yyyy/MM/dd HH:mm')-unix_timestamp(min(dt),'yyyy/MM/dd HH:mm'))/60 as time,count(browseid)
from t3
group by id;

--3.2
select id,(unix_timestamp(max(dt),'yyyy/MM/dd HH:mm')-unix_timestamp(min(dt),'yyyy/MM/dd HH:mm'))/60 as time,count(id)
from (select id,dt,mark,sum(mark)over(partition by id order by dt ) as diff_visit
      from (select id,dt,case when (unix_timestamp(dt,'yyyy/MM/dd HH:mm')-unix_timestamp(lag(dt) over(partition by id order by dt asc),'yyyy/MM/dd HH:mm'))/60>30 then 1
                else 0 end as mark 
            from t3
      )t4
)t5 
group by id,diff_visit;